
select * from tbl_CampaniasEmail
select id_EnvioEmail, id_CampaniaEmail, i_Enviados
from tbl_CampaniasEmailEnviadas
select * from tbl_UsuariosWeb where id_UsuarioWeb = 1

select * from tbl_segmentosCRM where id_Segmento =13;
select * from c3lmp.dbo.tbl_Guiones
 where id_guion=2;

--Tickets de No inscritos (con tarjeta preactiva)
select count(id_Evento) as tikets_noInscritos from tmp_evVentasUsuariosWeb
		where id_UsuarioWeb in (
			select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
		);

select top 15 * from tmp_evVentasUsuariosWeb order by dt_FechaVenta DESC;
--Pacientes que recientemente han comprado y no han activado su tarjeta
select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb 
left join tbl_UsuariosWebTelefonos
ON tbl_UsuariosWeb.id_UsuarioWeb = tbl_UsuariosWebTelefonos.id_UsuarioWeb
where tbl_UsuariosWeb.id_UsuarioWeb in (
	select id_UsuarioWeb  from tmp_evVentasUsuariosWeb
	where id_UsuarioWeb in (
		select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
	)
	AND id_UsuarioWeb NOT IN (
		SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones 
		WHERE (id_Guion = 2 
		AND id_CalificacionLlamada 
		IN(3,4,5,6,7,10,12,13,16,17,21,23,24))
		OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND (DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30))
	 )
	GROUP BY id_UsuarioWeb 
)
GROUP BY tbl_UsuariosWeb.id_UsuarioWeb 

SELECT * FROM c3lmp.dbo.app_LlamadasGuiones 
where (DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30)

--determinar estudios gratis de este paciente
SELECT count(id_UsuarioWeb) as total FROM tbl_UsuariosWeb WHERE id_UsuarioWeb = '138745' and id_Tarjetastatus=1



select * from tbl_tarjetasExpedidas where st_TarjetaExpedida ='329744'



--Hacerlo tanto en POS como en CRM
update tbl_UsuariosWeb SET st_tarjeta='15000', id_tarjetaStatus=2, id_OrigenRegistro=4 where id_UsuarioWeb = 1
update tbl_TarjetasExpedidas SET i_bloqueado=0, i_disponible=1 where st_TarjetaExpedida='150000'	--Solo CRM
--select * from tbl_TarjetasExpedidas where st_TarjetaExpedida='150000'
select * 
-- delete
from tbl_UsuariosWebConfirmados where id_UsuarioWeb = 1



select tbl_UsuariosWebTelefonos.id_UsuarioWeb from tbl_UsuariosWebTelefonos
where tbl_UsuariosWebTelefonos.id_UsuarioWeb in (
'75','177','213','226','242','610','611','640','667','748','827','971','1124','1415','1634','1643','1702','1876','2484','2587','2677','2754','2936','2953','3021','3217','3340','3437','3785','4008','4745','4812','4837','5122','5125','5189','5203','5408','5440','5599','5602','5603','5615','5620','5796','6006','6046','6329','7326','7349','7723','7926','8185','8270','8301','8549','8596','8695','8873','8906','8950','9162','9176','9535','9648','9736','9825','9857','10004','10146','10149','10211','10287','10385','10540','10604','10640','11272','11329','11636','11751','11754','11925','12025','12173','12187','12273','12358','12473','12928','12945','12947','12957','13193','13293','13395','13628','13655','13657','13678','13947','14123','14611','14884','15032','15073','15089','15159','15454','15860','15948','16078','16115','16457','16495','17089','17099','17228','17330','17372','17434','17463','17923','18063','18244','18387','18465','18551','19102','19340','19443','19505','19555','19588','19664','19825','19899','19923','19969','20194','20233','20263','20365','20486','20693','20738','20791','20918','21068','21094','21100','21118','21526','21546','21637','21744','21846','21965','22075','22203','22215','22298','22326','22468','22521','22809','22830','22867','22874','23135','23582','23591','23652','23675','23816','24007','24021','24286','24411','24607','24792','24962','25053','25148','25189','25344','25505','25570','25612','25646','25744','26261','26341','26691','26706','27082','27138','27178','27227','27240','27395','27552','27647','27942','27965','27968','28283','28512','28621','28767','28955','29796','29808','29825','29909','29943','30186','30629','30657','30828','31073','31121','31264','31328','31847','31872','32507','32541','32561','32601','32855','32957','33467','33486','33549','33669','33871','34012','34135','34281','34305','34595','35103','35571','35932','36327','36456','36533','36742','36786','37249','37357','37501','37854','38314','38504','38866','39163','39660','39753','40701','41056','41326','41956','42186','42444','42456','42492','42559','42659','42689','42897','43242','43724','43909','44054','44185','44247','44289','44310','44375','44377','44585','44741','44914','44951','44965','45439','45535','45738','45741','45766','45830','46029','46055','46231','46267','46530','46571','46585','46635','46825','46953','47198','47364','47381','47606','47670','47686','47868','47922','48038','48135','48465','48502','48610','48863','48869','48915','48923','48975','49122','49163','49166','49338','49442','49689','49731','49801','49845','49877','50034','50087','50144','50187','50192','50343','50624','50652','50682','50699','50752','50803','50847','51071','51093','51095','51237','51238','51239','51270','51319','51397','51409','51416','51468','51581','51622','51823','52006','52017','52073','52147','52172','52179','52252','52377','52403','52433','52450','52501','52514','52555','52832','52891','53137','53190','53208','53311','53490','53542','53605','53888','53953','53999','54004','54127','54256','54293','54311','54678','54697','54820','54832','54863','54865','54870','54966','55240','55304','55331','55452','55519','55542','55611','55620','55807','55922','55930','56028','56040','56306','56309','56381','56411','56536','56556','56616','56617','56639','56669','56701','56768','56799','56908','57005','57043','57077','57201','57252','57292','57318','57424','57467','57472','57530','57565','57676','57685','57701','57909','57995','58020','58110','58122','58125','58193','58329','58419','58459','58507','58543','58666','58683','58713','58801','58882','58887','58906','59053','59055','59176','59202','59203','59248','59314','59347','59393','59447','59496','59615','59628','59671','59687','59707','59717','59759','59781','59899','59943','59944','59956','59979','59992','60012','60068','60115','60118','60120','60164','60165','60216','60229','60311','60405','60484','60486','60538','60541','60608','60725','60733','60826','60875','60918','60919','60929','61016','61032','61154','61162','61232','61390','61394','61402','61429','61445','61446','61451','61475','61571','61778','61898','62021','62046','62055','62097','62105','62123','62176','62256','62333','62335','62374','62417','62491','62571','62705','62707','62745','62755','62778','62782','62856','62911','62916','62973','62996','63017','63136','63141','63297','63350','63373','63380','63422','63448','63540','63561','63604','63731','63767','63799','63883','63926','63957','63961','64055','64159','64466','64511','64515','64532','64580','64625','64675','64711','64750','64768','64781','64818','64843','64858','64861','64909','64956','64971','65001','65051','65179','65289','65373','65394','65404','65453','65509','65709','65728','65771','65776','65848','65871','65894','66096','66182','66235','66300','66341','66344','66380','66398','66400','66423','66449','66464','66482','66488','66494','66601','66634','66664','66682','66708','66748','66759','66792','66818','67043','67071','67114','67142','67238','67271','67279','67481','67551','67627','67640','67671','67742','67798','67854','67864','67894','67932','68030','68040','68057','68069','68179','68284','68388','68403','68414','68422','68423','68482','68495','68539','68617','68654','68736','68748','68758','68869','68870','69014','69053','69092','69093','69204','69311','69392','69425','69474','69521','69665','69699','69979','70279','70320','70758','70759','70911','70943','71031','71060','71101','71109','71110','71131','71150','71160','71164','71299','71368','71414','71418','71449','71528','71537','71599','71712','71742','71822','71846','71873','71939','72048','72121','72255','72395','72443','72580','72726','72743','72766','73058','73110','73117','73211','73312','73323','73336','73589','73690','73699','73752','73813','74134','74219','74322','74385','74626','74825','74866','74902','74991','75295','75327','75361','75678','75941','75976','76209','76218','76238','76558','76609','76653','76662','76784','76825','77035','77127','77244','77254','77376','77392','77415','77719','77742','77805','77915','78037','78202','78384','78767','79293','79340','79402','79685','79732','79776','79805','79919','80008','80047','80048','80105','80171','80179','80338','80383','80389','80517','80572','80577','80642','80686','80758','81031','81046','81116','81125','81153','81395','81401','81531','81744','81811','81875','82011','82037','82073','82095','82116','82239','82250','82260','82325','82421','82436','82630','82653','82786','82798','82989','83093','83110','83196','83345','83611','83641','83656','83687','84202','84616','84682','84949','85011','85231','85293','86020','86082','86099','86237','86312','86380','86405','86945','86997','87032','87452','87592','87620','87754','87763','88048','88880','88919','89179','89260','89266','89439','89812','89849','90059','90251','90393','90430','90499','90579','90690','90912','90944','91366','91393','91556','91600','91630','91772','91805','91837','92013','92140','92155','92285','92450','92521','92614','92681','92726','92824','92862','92905','92912','92960','93141','93177','93465','93604','93616','94035','94046','94270','94340','94449','94672','94708','94780','94835','95162','95296','95316','95348','95442','95495','95632','95636','95652','95703','95720','96065','96113','96135','96156','96216','96277','96280','96360','96508','96632','96663','96797','96803','96833','96936','97077','97151','97208','97230','97231','97259','97352','97428','97433','97455','97496','97550','97570','97705','97709','97741','97757','97776','97811','97926','97927','97947','97975','98027','98119','98178','98186','98232','98300','98373','98397','98575','98694','98734','98798','98937','99187','99266','99290','99339','99341','99447','99730','99745','99926','100020','100060','100144','100205','100219','100250','100255','100292','100338','100637','100666','100862','100890','100918','101157','101193','101371','101399','101401','101428','101439','101465','101493','101583','101688','101710','101792','101938','101967','102045','102120','102167','102169','102204','102249','102508','102523','102592','102595','102599','102602','102659','102712','103055','103092','103218','103366','103403','103450','103596','103670','103677','103693','103824','103932','103951','104008','104049','104065','104157','104196','104379','104498','104501','104618','104634','104668','104673','104801','104886','105045','105160','105265','105337','105448','105460','105469','105492','105539','105552','105665','105697','105775','105780','105846','105909','105911','105912','105965','105999','106043','106167','106171','106181','106502','106512','106553','106673','106678','106727','106872','106874','107086','107270','107380','107384','107417','107433','107528','107612','107627','107637','107645','107683','107804','107850','107863','107945','107958','108108','108214','108345','108435','108440','108451','108496','108530','108604','108626','108756','108830','108892','108933','108978','109008','109066','109071','109138','109219','109304','109328','109430','109536','109545','109570','109600','109624','109671','109685','109703','109750','109795','109839','110075','110126','110162','110174','110422','110459','110498','110571','110607','110738','110741','110790','110791','111075','111147','111152','111186','111230','111232','111254','111287','111321','111387','111403','111480','111512','111566','111663','111669','111722','111866','111980','112188','112200','112257','112318','112334','112338','112360','112695','112822','112865','113130','113186','113250','113315','113385','113433','113481','113538','113543','113629','113643','113653','113887','113893','113980','114105','114314','114341','114343','114495','114567','114573','114647','114680','114976','115033','115199','115477','115646','115714','115797','115876','115971','115973','116001','116021','116052','116121','116157','116300','116432','116443','116454','116488','116581','116658','116697','116763','116783','116980','116981','117033','117078','117088','117242','117246','117280','117282','117390','117592','117600','117606','117613','117656','117673','117757','117783','117784','117864','118144','118260','118298','118324','118349','118678','118784','118926','118991','119127','119173','119413','119447','119502','119514','119751','119880','119889','119985','120158','120369','120530','120574','120673','120907','120920','121129','121330','121355','121376','121490','121595','121668','121720','121854','121980','122218','122248','122261','122532','122938','122979','123117','123146','123162','123163','123233','123410','123417','123509','123543','123547','123632','123654','123667','123806','123857','124112','124364','124491','124548','124815','124856','124857','124865','124869','125251','125386','125489','125610','126593','126689','126831','127173','127426','127443','127566','127605','127643','127662','127697','127755','127967','127981','128053','128383','128534','128739','128753','128914','128989','129040','129105','129110','129362','129593','129782','129810','130026','130421','130525','130580','130634','130728','130775','130821','130896','131068','131223'
)
group by tbl_UsuariosWebTelefonos.id_UsuarioWeb

--ordenar por telefono
SELECT     *
				FROM      (
	select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb 
	left join tbl_UsuariosWebTelefonos
	ON tbl_UsuariosWeb.id_UsuarioWeb = tbl_UsuariosWebTelefonos.id_UsuarioWeb
	where tbl_UsuariosWeb.id_UsuarioWeb in (
		select id_UsuarioWeb  from tmp_evVentasUsuariosWeb
		where id_UsuarioWeb in (
			select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
		)
		AND id_UsuarioWeb NOT IN (
			SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones 
			WHERE (id_Guion = 2 
			AND id_CalificacionLlamada 
			IN(3,4,5,6,7,10,12,13,16,17,21,23,24))
			OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30))
		 )
		GROUP BY id_UsuarioWeb 
	)
	GROUP BY tbl_UsuariosWeb.id_UsuarioWeb 
) newtable2
ORDER BY id_UsuarioWeb desc










--Pacientes con telefono
select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb 
where tbl_UsuariosWeb.id_UsuarioWeb in (
	select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb 
	where tbl_UsuariosWeb.id_UsuarioWeb in (
		select id_UsuarioWeb from tbl_UsuariosWebTelefonos
	) AND tbl_UsuariosWeb.id_UsuarioWeb in (
		select id_UsuarioWeb  from tmp_evVentasUsuariosWeb
		where id_UsuarioWeb in (
			select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
		)
		AND id_UsuarioWeb NOT IN (
			SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones 
			WHERE (id_Guion = 2 
			AND id_CalificacionLlamada 
			IN(3,4,5,6,7,10,12,13,16,17,21,23,24))
			OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30))
		 )
		GROUP BY id_UsuarioWeb 
	)
	GROUP BY id_UsuarioWeb 
	UNION ALL 
	select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb 
	where tbl_UsuariosWeb.id_UsuarioWeb in (
		select id_UsuarioWeb  from tmp_evVentasUsuariosWeb
		where id_UsuarioWeb in (
			select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
		)
		AND id_UsuarioWeb NOT IN (
			SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones 
			WHERE (id_Guion = 2 
			AND id_CalificacionLlamada 
			IN(3,4,5,6,7,10,12,13,16,17,21,23,24))
			OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30))
		 )
		GROUP BY id_UsuarioWeb 
	)
	GROUP BY id_UsuarioWeb 
)
GROUP BY id_UsuarioWeb 


select * from tmp_evVentasUsuariosWeb
where id_UsuarioWeb in ( 242 );






SELECT     *
				FROM      (SELECT     TOP 10000 *
                      	    FROM   (SELECT TOP 10000 tbl_UsuariosWeb.*, cat_Estados.abr_Estado AS Estado, cat_Municipios.st_Municipio AS Municipio
FROM         dbo.tbl_UsuariosWeb LEFT OUTER JOIN
                      dbo.cat_Estados ON dbo.tbl_UsuariosWeb.id_Estado = dbo.cat_Estados.id_Estado LEFT OUTER JOIN
                      dbo.cat_Municipios ON dbo.tbl_UsuariosWeb.id_Municipio = dbo.cat_Municipios.id_Municipio
WHERE     dbo.tbl_UsuariosWeb.id_UsuarioWeb IN (

select id_UsuarioWeb  from tmp_evVentasUsuariosWeb
where id_UsuarioWeb in (
	select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1
)
AND id_UsuarioWeb NOT IN (
	SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones 
	WHERE (id_Guion in(2,8)
	AND id_CalificacionLlamada 
	IN(3,4,5,6,7,10,12,13,16,17,21,23,24))
	OR (id_Guion in(2,8) AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30))
 )
GROUP BY id_UsuarioWeb 

)

ORDER BY dt_FechaRegistro desc) newtable1
	            ORDER BY dt_FechaRegistro asc) newtable2
				ORDER BY dt_FechaRegistro desc



SELECT * FROM (
	SELECT TOP 10 * FROM (
		SELECT TOP 10 tbl_UsuariosWeb.*, cat_Estados.abr_Estado AS Estado, cat_Municipios.st_Municipio AS Municipio
		FROM dbo.tbl_UsuariosWeb 
		LEFT OUTER JOIN dbo.cat_Estados 
		ON dbo.tbl_UsuariosWeb.id_Estado = dbo.cat_Estados.id_Estado 
		LEFT OUTER JOIN dbo.cat_Municipios 
		ON dbo.tbl_UsuariosWeb.id_Municipio = dbo.cat_Municipios.id_Municipio 
		WHERE dbo.tbl_UsuariosWeb.id_UsuarioWeb IN (select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWebTelefonos ) AND tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tmp_evVentasUsuariosWeb where id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1 ) AND id_UsuarioWeb NOT IN ( SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones WHERE (id_Guion = 2 AND id_CalificacionLlamada IN(3,4,5,6,7,10,12,13,16,17,21,23,24)) OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30)) ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb UNION ALL select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tmp_evVentasUsuariosWeb where id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1 ) AND id_UsuarioWeb NOT IN ( SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones WHERE (id_Guion = 2 AND id_CalificacionLlamada IN(3,4,5,6,7,10,12,13,16,17,21,23,24)) OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30)) ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb )
			ORDER BY id_UsuarioWeb asc
	) newtable1 ORDER BY id_UsuarioWeb desc
) newtable2 ORDER BY id_UsuarioWeb asc	


SELECT * FROM (
	SELECT TOP 10 * FROM (
		SELECT TOP 20 tbl_UsuariosWeb.*, cat_Estados.abr_Estado AS Estado, cat_Municipios.st_Municipio AS Municipio
		FROM dbo.tbl_UsuariosWeb 
		LEFT OUTER JOIN dbo.cat_Estados 
		ON dbo.tbl_UsuariosWeb.id_Estado = dbo.cat_Estados.id_Estado 
		LEFT OUTER JOIN dbo.cat_Municipios 
		ON dbo.tbl_UsuariosWeb.id_Municipio = dbo.cat_Municipios.id_Municipio 
		WHERE dbo.tbl_UsuariosWeb.id_UsuarioWeb IN (select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWebTelefonos ) AND tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tmp_evVentasUsuariosWeb where id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1 ) AND id_UsuarioWeb NOT IN ( SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones WHERE (id_Guion = 2 AND id_CalificacionLlamada IN(3,4,5,6,7,10,12,13,16,17,21,23,24)) OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30)) ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb UNION ALL select tbl_UsuariosWeb.id_UsuarioWeb from tbl_UsuariosWeb where tbl_UsuariosWeb.id_UsuarioWeb in ( select id_UsuarioWeb from tmp_evVentasUsuariosWeb where id_UsuarioWeb in ( select id_UsuarioWeb from tbl_UsuariosWeb where id_TarjetaStatus<>1 ) AND id_UsuarioWeb NOT IN ( SELECT id_UsuarioWeb FROM c3lmp.dbo.app_LlamadasGuiones WHERE (id_Guion = 2 AND id_CalificacionLlamada IN(3,4,5,6,7,10,12,13,16,17,21,23,24)) OR (id_Guion = 2 AND id_CalificacionLlamada IN(1,18) AND(DATEDIFF(dd,fechaEjecucion, GETDATE()) <= 30)) ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb ) GROUP BY id_UsuarioWeb )
			ORDER BY id_UsuarioWeb asc
	) newtable1 ORDER BY id_UsuarioWeb desc
) newtable2 ORDER BY id_UsuarioWeb asc	


 SELECT newid() AS 'New id',
   RAND() AS 'Random',
   Checksum(newid()) AS 'Checksum', 
abs(checksum(newid())) as 'Checksum_newid'
GO 3
SELECT  abs(checksum(newid()))  % 10  as id order by id asc
GO 3

